﻿

-- 被过滤掉的考勤记录
CREATE view [dbo].[vwRyFilterKqjl]
as
select jsdm,sfzh,xm,qybmm,kh,kbs,case 
		when (kqsj<sjd1ks and jczt=1) then sjd1ks 
		when (kqsj>sjd1js and jczt=0) then sjd1js 
		else kqsj end kqsj,
jczt,sfdj,kqlx,pbsj,sjd1ks sjdbegin,sjd1js sjdend from vwRyDetailKqjl
where kqsj<isnull(sjd2ks,convert(varchar(10),sjd1js,120) +' 23:59:59')
union
select jsdm,sfzh,xm,qybmm,kh,kbs,case 
	    when (kqsj<sjd2ks and jczt=1) then sjd2ks 
		when (kqsj>sjd2js and jczt=0) then sjd2js 
		else kqsj end kqsj,
jczt,sfdj,kqlx,pbsj,sjd2ks,sjd2js from vwRyDetailKqjl
where kqsj>=sjd1js and kqsj<isnull(sjd3ks,convert(varchar(10),sjd2js,120) +' 23:59:59')
union
select jsdm,sfzh,xm,qybmm,kh,kbs,case 
	    when (kqsj<sjd3ks and jczt=1) then sjd3ks 
		when (kqsj>sjd3js and jczt=0) then sjd3js 
		else kqsj end kqsj,jczt,sfdj,kqlx,pbsj,sjd3ks,sjd3js from vwRyDetailKqjl
where kqsj>=sjd2js and kqsj<isnull(sjd4ks,convert(varchar(10),sjd3js,120) +' 23:59:59')
union
select jsdm,sfzh,xm,qybmm,kh,kbs,case 
	    when (kqsj<sjd4ks and jczt=1) then sjd4ks 
		when (kqsj>sjd4js and jczt=0) then sjd4js 
		else kqsj end kqsj,jczt,sfdj,kqlx,pbsj,sjd4ks,sjd4js from vwRyDetailKqjl
where kqsj>=sjd3js